﻿CREATE PROCEDURE [dbo].[proc_Orders_Create]
	(
		@CustomerId nvarchar(50),
		@UserName nvarchar(50),
		@ContactPerson nvarchar(50),
		@ContactPhone nvarchar(50),
		@FixtureDate datetime,
		@Description text,
		@Aggregateamount numeric(18,2),
		@Proceeds numeric(18,2),
		@CreateDate datetime,
		@FinanceFlag bit,
		@PaymentTypeId int,
		@BaiDuAccount nvarchar(50),
		@MainProjectId int
	)
AS
	begin tran
	--算法开始
		declare @OrderId nvarchar(50)
		declare @CompanyId int
		declare @DepartmentId int
		declare @sql nvarchar(2000)
		declare @return nvarchar(20)		
		 set @sql='
            declare @nowdate nvarchar(10)
		    declare @mincoding nvarchar(20)
            declare @coding nvarchar(20)        
            set @nowdate=convert(nvarchar(10),@FixtureDate,120)
            set @nowdate=replace(@nowdate,''-'','''')        
            set @mincoding=@nowdate+replicate(''0'',3)+''0''            
            select @coding=isnull(max(orderid),@mincoding) from Orders where orderid like @nowdate+''%''	
			set @return=convert(nvarchar,convert(numeric(18,0),@coding)+1)         
        '
        exec sp_executesql @sql,N'@FixtureDate datetime,@return nvarchar(20) output',@FixtureDate,@return output
        --算法结束
    Set @OrderId = @return
	Set	Exec @CompanyId=proc_Company_GetID @UserName,@CompanyId
	Set Exec @DepartmentId=proc_Department_GetID @UserName,@DepartmentId
	
	declare @CustomerName nvarchar(50),@EName nvarchar(50)
	Select @CustomerName=CustomerName From Customer Where CustomerId=@CustomerId
	Select @EName=EName From Employee Where UserName=@UserName
	Insert Into Orders
	(
	OrderId,
	CustomerId,
	CustomerName,
	ContactPerson,
	ContactPhone,
	UserName,
	FixtureDate,
	Description,
	Aggregateamount,
	Proceeds,
	CreateDate,
	CompanyId,
	DepartmentId,
	FinanceFlag,
	PaymentTypeId,
	BaiDuAccount,
	MainProjectId,
	EName
	)
	Values
	(
	@OrderId,
	@CustomerId,
	@CustomerName,
	@ContactPerson,
	@ContactPhone,
	@UserName,
	@FixtureDate,
	@Description,
	@Aggregateamount,
	@Proceeds,
	@CreateDate,
	@CompanyId,
	@DepartmentId,
	@FinanceFlag,
	@PaymentTypeId,
	@BaiDuAccount,
	@MainProjectId,
	@EName
	)
	
	Update Customer Set StateId=2,ProtectId=3,ProtectName=NULL,ProtectDate=NULL Where CustomerId=@CustomerId
	commit tran
	RETURN